<?php

namespace pod;

/**
* 
*/
class SqliteDB
{

  protected $db;

  public function __construct($dbFile= DATA_DIR."/app/device.db"){
    # code...
    $this->db = new \SQLite3($dbFile);
    $this->initDb($dbFile);
  }

    /**
     * 初始化数据库
     *
     * @param string $dbFile 数据库文件位置
     * @return boolean 成功与否
     */
    protected function initDb($dbFile){
      $initSql = '
      CREATE TABLE IF NOT EXISTS "udid" (
      "id"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
      "devicename"  TEXT,
      "username"  TEXT,
      "device"  TEXT,
      "deviceversion"  TEXT,
      "system"  TEXT,
      "udid"  TEXT,
      "imei"  TEXT,
      "savetime"  INTEGER
      );
      ';
      return $this->db->exec($initSql);
    }

    public function save($username,$device,$devicename,$udid,$deviceversion,$system,$imei="")
    {
        # code...
      $sql = "INSERT INTO main.udid('username','device','devicename','udid','deviceversion','system','imei','savetime') VALUES ('{$username}','{$device}','$devicename','$udid','{$deviceversion}','{$system}','{$imei}',strftime('%s','now'))";
        /*try {
          $this->db->exec($sql);
        } catch (Exception $e) {
          $this->db->exec($sql);
        }*/
        $ret = $this->db->exec($sql);
        //echo $sql;
      }


      public function queryUdid($udid)
      {
        # code...
        $sql = "SELECT * FROM main.udid  where udid like '%udid%'";
        $ret = $this->db->query($sql);

        $index = 1;
        $data = array();
        if ($ret) {
         while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
          $index++;
          $data[] = $row;// array('time'=>$row["time"],'saveTime'=>date( "Y-m-d H:i:s", $row["savetime"]),'source'=>$row["source"],'level'=>$row["level"],'content'=>$row["data"]);
        }
        //echo json_encode(array('status'=> 1,'data' => $data));
      }else{
        //echo json_encode(array('res'=> 0,'data' => $data,"sql"=>$sql));
      }
    }

    public function queryUdids()
    {
        # code...
      $sql = "select * from  (SELECT * FROM main.udid  order by savetime DESC) GROUP BY udid order by savetime DESC";
        /*try {
          $this->db->exec($sql);
        } catch (Exception $e) {
          $this->db->exec($sql);
        }*/
        $ret = $this->db->query($sql);

        $index = 1;
        $data = array();
        if ($ret) {
         while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
          $index++;
          $data[] = $row;// array('time'=>$row["time"],'saveTime'=>date( "Y-m-d H:i:s", $row["savetime"]),'source'=>$row["source"],'level'=>$row["level"],'content'=>$row["data"]);
        }
        //echo json_encode(array('status'=> 1,'data' => $data));
      }else{
        //echo json_encode(array('res'=> 0,'data' => $data,"sql"=>$sql));
      }
      return $data;
    }


  public function delete($id)
  {
    $sql = "DELETE  FROM main.log WHERE id=$id;";//,strftime('%s','now')
    return $this->safeExecSql($sql);
  }



    public function queryApp($id)
    {
        # code...
      $sql = "select * from main.log  WHERE id=$id;";
        /*try {
          $this->db->exec($sql);
        } catch (Exception $e) {
          $this->db->exec($sql);
        }*/
        $ret = $this->db->query($sql);

        $index = 1;
        $data = array();
        if ($ret) {
         while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
          $index++;
          $data[] = $row;// array('time'=>$row["time"],'saveTime'=>date( "Y-m-d H:i:s", $row["savetime"]),'source'=>$row["source"],'level'=>$row["level"],'content'=>$row["data"]);
        }
        //echo json_encode(array('status'=> 1,'data' => $data));
      }else{
        //echo json_encode(array('res'=> 0,'data' => $data,"sql"=>$sql));
      }

      return $data;
    }


    function safeExecSql($sql){
      $flag = 2;
      while( $flag>0 ){
        $ret = $this->db->exec($sql);
        $flag --;
        if(!$ret){
          sleep(1);
          continue;
        } else {
          break;
        }
      }
    }


    function __destruct(){
      if ( ($this->db instanceof SQLite3)) {
        $this->db->close();
      }
    }

  }
